﻿/*
=============================================================================
Author:魏宝辉通过CodeSmithe生成
CreatedTime:2012-9-9
Description:HotelInfo三层结构数据访问层
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using FTchina.Models;//引入模型层
namespace FTchina.DAL
{
	public static partial class HotelInfoService
	{
		#region 增加一条数据
		public static int AddHotelInfo(HotelInfo hotelInfo)
		{
			string sql =
			"INSERT INTO HotelInfo(id,HotelName,HotelAddress,HotelTel,OpenDinner,OpenRoom) "+
			"VALUES (@id,@HotelName,@HotelAddress,@HotelTel,@OpenDinner,@OpenRoom)";
			
			 
				SQLiteParameter[] para = new SQLiteParameter[]
				{
						
					new SQLiteParameter("@id",hotelInfo.Id),
						
					new SQLiteParameter("@HotelName",hotelInfo.HotelName),
						
					new SQLiteParameter("@HotelAddress",hotelInfo.HotelAddress),
						
					new SQLiteParameter("@HotelTel",hotelInfo.HotelTel),
						
					new SQLiteParameter("@OpenDinner",hotelInfo.OpenDinner),
						
					new SQLiteParameter("@OpenRoom",hotelInfo.OpenRoom)
				};
				int newId = DBHelper.ExecuteCommand(sql, para);
				return newId;
			 
		}
		#endregion
		#region 删除数据以主键，标识列，唯一列构建的删除
			
		public static int DeleteHotelInfoById(long id)	
		{
			string sql="DELETE From  HotelInfo  Where id=@id";
			 SQLiteParameter[] para = new SQLiteParameter[]
				{
					new SQLiteParameter("@id", id)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}		
		
		#endregion
		#region 修改数据
		//考虑标识列与主键不一致情形
		//以主键数据修改实体
		public static int ModifyHotelInfoByid(HotelInfo hotelInfo)
		{
		string sql ="UPDATE "+"HotelInfo"+" set "+
					"HotelName=@HotelName, "+
					"HotelAddress=@HotelAddress, "+
					"HotelTel=@HotelTel, "+
					"OpenDinner=@OpenDinner, "+
					"OpenRoom=@OpenRoom "+ 
				" Where id=@id";
			 
				SQLiteParameter[] para = new SQLiteParameter[]
				{
					new SQLiteParameter("@id",hotelInfo.Id),
					new SQLiteParameter("@HotelName",hotelInfo.HotelName),
					new SQLiteParameter("@HotelAddress",hotelInfo.HotelAddress),
					new SQLiteParameter("@HotelTel",hotelInfo.HotelTel),
					new SQLiteParameter("@OpenDinner",hotelInfo.OpenDinner),
					new SQLiteParameter("@OpenRoom",hotelInfo.OpenRoom)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}				
		#endregion
		#region 查询数据
		public static IList<HotelInfo> GetAllHotelInfos()
		{
			string sqlAll = "SELECT * FROM HotelInfo";
			return GetHotelInfosBySql(sqlAll);
		}
		//以标识列获得对象
		//以主键查询
		public static HotelInfo GetHotelInfoByid(long id)
		{
			string sql = "SELECT * FROM "+"HotelInfo"+" WHERE id = @id";
			 using(SQLiteDataReader reader = DBHelper.GetReader(sql, new SQLiteParameter("@id", id)))
				{
                if (reader.Read())
				{
					HotelInfo hotelInfo = new HotelInfo();
					hotelInfo.Id=Convert.ToInt32(reader["id"]);
					hotelInfo.HotelName=Convert.ToString(reader["HotelName"]);
					hotelInfo.HotelAddress=Convert.ToString(reader["HotelAddress"]);
					hotelInfo.HotelTel=Convert.ToString(reader["HotelTel"]);
					hotelInfo.OpenDinner=Convert.ToInt32(reader["OpenDinner"]);
					hotelInfo.OpenRoom=Convert.ToInt32(reader["OpenRoom"]);
					reader.Close();
					
					return hotelInfo;
				}
				else
				{
					reader.Close();
					return null;
				}
                }
			 
		}			
		//私有方法供构建好的SQL语句调用
		private static IList<HotelInfo> GetHotelInfosBySql(string safeSql)
		{
			List<HotelInfo> list = new List<HotelInfo>();
			 
            using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					HotelInfo hotelInfo = new HotelInfo();
					hotelInfo.Id=Convert.ToInt32(row["id"]);
					hotelInfo.HotelName=Convert.ToString(row["HotelName"]);
					hotelInfo.HotelAddress=Convert.ToString(row["HotelAddress"]);
					hotelInfo.HotelTel=Convert.ToString(row["HotelTel"]);
					hotelInfo.OpenDinner=Convert.ToInt32(row["OpenDinner"]);
					hotelInfo.OpenRoom=Convert.ToInt32(row["OpenRoom"]);
					list.Add(hotelInfo);
				}
				return list;
			}
             
		}
		private static IList<HotelInfo> GetHotelInfosBySql( string sql, params SQLiteParameter[] values )
		{
			List<HotelInfo> list = new List<HotelInfo>();
			 
            using (DataTable table = DBHelper.GetDataTable(sql, values))
			{
				foreach (DataRow row in table.Rows)
				{
					HotelInfo hotelInfo = new HotelInfo();
					hotelInfo.Id=Convert.ToInt32(row["id"]);
					hotelInfo.HotelName=Convert.ToString(row["HotelName"]);
					hotelInfo.HotelAddress=Convert.ToString(row["HotelAddress"]);
					hotelInfo.HotelTel=Convert.ToString(row["HotelTel"]);
					hotelInfo.OpenDinner=Convert.ToInt32(row["OpenDinner"]);
					hotelInfo.OpenRoom=Convert.ToInt32(row["OpenRoom"]);
					list.Add(hotelInfo);
				}
				return list;
			}
            
			 
		}
		#endregion
		 
	}
}

